Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Part IV
Tuning SQL

Chapter 24 What Is a Well-Tuned SQL Statement?
25 Using EXPLAIN PLAN and SQL Trace
26 Tuning SQL Statements
27 Using the Oracle Optimizer
28 Using Procedures, Functions, and Packages
29 Providing for Data Integrity and Triggers
30 Using Hints
31 Introducing SQL Development Tools
32 Miscellaneous SQL Topics

In Part III of this book, you learned about the various ways to tune for specific application types. You learned not only how to tune the RDBMS and the server operating system, but also how to lay out the data to take advantage of data access patterns. You examined some specific operations and configurations such as the Oracle Parallel Server system and optimal backup and recovery. These examples and the information in Part II, “Tuning the Server,” should give you a good idea how to optimize the Oracle server system.

Part IV leaves the Oracle server and starts looking at the client side of the system. The following chapters analyze SQL statements and determine how these statements can be optimized, both for efficiency and to reduce unnecessary processing on the server. Part V, “Tuning the Client,” finishes the discussion of the client side of the system by looking at specific ways to optimize the client itself.

Tuning and optimizing the server is very important but cannot make up for a badly designed application. The server is designed to optimally process the SQL requests and return the data that is requested. Part II of this book showed you how to tune this process. It is possible, however for incorrectly designed SQL statements to scan massive amounts of unnecessary data and bypass well-designed indexes.

In Part IV, you learn how to determine whether your SQL statements are performing unnecessary functions and causing unwanted data to be returned to your client system. You learn how to use Oracle tools such as SQL Trace and EXPLAIN PLAN to determine whether your SQL statements are inefficient. You look at how to take advantage of the Oracle optimizer and how to use procedures and packages to best use the shared SQL area of the shared pool. Finally, you look at some products available to help you debug and optimize SQL statements.

When you finish the chapters in this part of the book, you should be able to determine the best way to form an SQL statement. You will be able to take advantage of features such as the Oracle Parallel Query option and to make best use of indexes, clusters, and hash clusters. You should also be able to use the SQL Trace and EXPLAIN PLAN utilities and analyze SQL statements with them.

Chapter 24
What Is a Well-Tuned SQL Statement?

This chapter begins to look at ways to produce a well-tuned application. The key to having an optimized application is at the heart of the application: the SQL statements themselves. Optimizing your SQL statements and reducing unnecessary overhead will result in an optimized application.

An optimized application together with the tuned and optimized RDBMS server will provide a well-balanced, highly tuned system. Because users are mainly interested in response times, having both a well-tuned application and an optimized server is essential. To get an optimized application, you must start with an optimized SQL statement.

So, what is a well-tuned, optimized SQL statement? Here’s a list of some of the characteristics of a well-tuned SQL statement:

  Makes efficient use of RDBMS features. The well-tuned SQL statement uses indexes or hashing as available. If possible, the application should also take advantage of features such as array processing and discrete transactions.
  Uses PL/SQL to improve performance. PL/SQL allows blocks of statements to be sent to the Oracle server at one time. If you don’t use PL/SQL, you must send each statement individually.
  Uses stored procedures. By using stored procedures, you reduce the amount of data that must be sent across the network and increase the chance that the statement may already be parsed in the shared SQL area.
  Uses packages. Packages increase performance because the entire package is loaded when the package is called for the first time.
  Uses cached sequences to generate primary key values. This improves the performance of key generation and makes it unnecessary to generate the key in the application.
  Makes efficient use of space. The SQL statement uses the VARCHAR2 data type instead of CHAR, when possible, to avoid unnecessary blank padding.
  Uses hints where necessary. A well-tuned SQL statement uses hints where appropriate to allow the programmer’s understanding of the SQL statement and the database design to override Oracle’s choice of optimization method.

These attributes in conjunction with your own specific attributes make a well-tuned SQL statement in your configuration. The properly tuned SQL statement avoids unnecessary functions and executes with the minimum amount of resources necessary to perform its function.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.